﻿using DAL.Comm;
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class EmployeeDAL
    {
        private SqlCommand cmd; //实例化/新建SqlCommand对象
        public EmployeeDAL()
        {
            cmd = new SqlCommand();  //实例化Command对象
            cmd.CommandType = CommandType.StoredProcedure;//设定Command对象的访问方式为存储过程
        }
        /// <summary>
        /// 添加参数方法实现
        /// </summary>
        private void SetPara(Employee bo)
        {
            cmd.Parameters.Add("employeeID", SqlDbType.NVarChar, 6).Value = bo.EmployeeID;
            cmd.Parameters.Add("employeeName", SqlDbType.NVarChar, 80).Value = bo.EmployeeName;
            cmd.Parameters.Add("birthday", SqlDbType.DateTime).Value = bo.Birthday;
            cmd.Parameters.Add("sex", SqlDbType.Bit).Value = bo.Gender;
            cmd.Parameters.Add("brief", SqlDbType.NVarChar, -1).Value = bo.Brief;
        }
        private bool ForEmployee(SqlCommand cmd)
        {
            try
            {
                int num = SQLHelper.ExecuteSQL(cmd);
                if (num > 0)
                    return true;
                else return false;
            }
            catch
            {
                return false;
            }
        }
        /// <summary>
        /// 获取员工记录
        /// </summary>
        public List<Employee> GetList()
        {
            cmd.CommandText = "SelectEmployeeList";
            cmd.Parameters.Clear();

            List<Employee> list = new List<Employee>();      //定义泛型集合list

            SqlDataReader read = SQLHelper.ExcuteReader(cmd);//读数据库记录到对象read中
            while (read.Read())
            {
                Employee bo = new Employee();            //定义Employee对象bo
                //读read对象中的记录，并将结果一一赋值给bo对象
                bo.EmployeeID = read["employeeid"].ToString().Trim();
                bo.EmployeeName = read["employeename"].ToString().Trim();
                bo.Birthday = Convert.ToDateTime(read["birthday"].ToString().Trim());
                bo.Gender = Convert.ToBoolean(read["sex"]);
                bo.Brief = read["brief"].ToString();

                list.Add(bo);           //将对象bo添加到泛型集合中
            }

            read.Close();       //关闭对象连接
            return list;
        }
        public bool Insert(Employee bo)
        {
            cmd.CommandText = "InsertEmployee";
            cmd.Parameters.Clear();
            SetPara(bo);
            return ForEmployee(cmd);
        }
        public bool Update(Employee bo)
        {
            cmd.CommandText = "UpdateEmployee";
            cmd.Parameters.Clear();
            SetPara(bo);
            return ForEmployee(cmd);
        }
        public bool Delete(Employee bo)
        {
            cmd.CommandText = "DeleteEmployee";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("employeeID", SqlDbType.NVarChar, 6).Value = bo.EmployeeID;
            return ForEmployee(cmd);
        }
        private Employee GetEmployeeForDataRead(SqlDataReader read)
        {
            string ID = read["employeeID"].ToString().Trim();
            string name = read["employeeName"].ToString().Trim();
            DateTime birthday = Convert.ToDateTime(read["birthday"].ToString());
            bool gender = Convert.ToBoolean(read["sex"]);
            string brief = read["brief"].ToString();
            Employee e = new Employee(ID, name, gender, birthday, brief);
            return e;
        }
        public Employee FindByID(string id)
        {
            cmd.CommandText = "FindEmployeeByID";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("employeeID", SqlDbType.NVarChar, 6).Value = id;
            SqlDataReader read = SQLHelper.ExcuteReader(cmd);
            while (read.Read())
            {
                Employee e = GetEmployeeForDataRead(read);
                read.Close();
                return e;
            }
            read.Close();
            return null;
        }
        public string FindIDByName(string name)
        {
            cmd.CommandText = "FindEmployeeIDByName";
            cmd.Parameters.Clear();
            cmd.Parameters.Add("name", SqlDbType.NVarChar, 80).Value = name;
            return SQLHelper.GetScalar(cmd).ToString();
        }
    }
}
